-- PostgreSQL DDL Script for Gateway Management System
-- Generated automatically for modular gateway system

-- 用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    phone VARCHAR(20),
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'locked')),
    last_login TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INTEGER REFERENCES users(id)
);

-- 角色表
CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    role_name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    permissions JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 用户角色关联表
CREATE TABLE user_roles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, role_id)
);

-- 菜单表
CREATE TABLE menus (
    id SERIAL PRIMARY KEY,
    parent_id INTEGER REFERENCES menus(id),
    menu_name VARCHAR(100) NOT NULL,
    menu_path VARCHAR(200),
    menu_icon VARCHAR(50),
    menu_type VARCHAR(20) DEFAULT 'menu' CHECK (menu_type IN ('menu', 'button', 'api')),
    sort_order INTEGER DEFAULT 0,
    is_visible BOOLEAN DEFAULT true,
    permissions JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 网关配置表
CREATE TABLE gateway_configs (
    id SERIAL PRIMARY KEY,
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value JSONB NOT NULL,
    description TEXT,
    config_type VARCHAR(50) DEFAULT 'system',
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INTEGER REFERENCES users(id)
);

-- 路由规则表
CREATE TABLE route_rules (
    id SERIAL PRIMARY KEY,
    rule_name VARCHAR(100) NOT NULL,
    match_pattern VARCHAR(500) NOT NULL,
    target_url VARCHAR(500) NOT NULL,
    method VARCHAR(10) DEFAULT 'ANY' CHECK (method IN ('GET', 'POST', 'PUT', 'DELETE', 'ANY')),
    priority INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    timeout_ms INTEGER DEFAULT 30000,
    retry_count INTEGER DEFAULT 0,
    rate_limit INTEGER DEFAULT 0,
    headers_config JSONB DEFAULT '{}',
    rewrite_config JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INTEGER REFERENCES users(id)
);

-- 网关统计表
CREATE TABLE gateway_stats (
    id SERIAL PRIMARY KEY,
    route_rule_id INTEGER REFERENCES route_rules(id),
    request_count BIGINT DEFAULT 0,
    success_count BIGINT DEFAULT 0,
    error_count BIGINT DEFAULT 0,
    avg_response_time DECIMAL(10,3) DEFAULT 0,
    last_request_time TIMESTAMP,
    stat_date DATE DEFAULT CURRENT_DATE,
    UNIQUE(route_rule_id, stat_date)
);

-- 操作日志表
CREATE TABLE operation_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    module VARCHAR(50) NOT NULL,
    action VARCHAR(50) NOT NULL,
    target_id INTEGER,
    description TEXT,
    ip_address INET,
    user_agent TEXT,
    request_data JSONB,
    response_data JSONB,
    status_code INTEGER,
    execution_time INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 系统配置表
CREATE TABLE system_configs (
    id SERIAL PRIMARY KEY,
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value TEXT NOT NULL,
    config_type VARCHAR(50) DEFAULT 'system',
    description TEXT,
    is_encrypted BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);
CREATE INDEX idx_user_roles_role_id ON user_roles(role_id);
CREATE INDEX idx_menus_parent_id ON menus(parent_id);
CREATE INDEX idx_menus_sort_order ON menus(sort_order);
CREATE INDEX idx_gateway_configs_key ON gateway_configs(config_key);
CREATE INDEX idx_route_rules_pattern ON route_rules(match_pattern);
CREATE INDEX idx_route_rules_active ON route_rules(is_active);
CREATE INDEX idx_gateway_stats_date ON gateway_stats(stat_date);
CREATE INDEX idx_operation_logs_user_id ON operation_logs(user_id);
CREATE INDEX idx_operation_logs_module ON operation_logs(module);
CREATE INDEX idx_operation_logs_created_at ON operation_logs(created_at);

-- 插入默认数据
INSERT INTO roles (role_name, description, permissions) VALUES 
('super_admin', '超级管理员', '{"*": "*"}'),
('admin', '管理员', '{"users": ["read", "write"], "gateway": ["read", "write"], "menus": ["read"]}'),
('user', '普通用户', '{"gateway": ["read"]}');

INSERT INTO menus (menu_name, menu_path, menu_icon, menu_type, sort_order) VALUES 
('仪表板', '/dashboard', 'dashboard', 'menu', 1),
('网关管理', '/gateway', 'gateway', 'menu', 2),
('路由配置', '/gateway/routes', 'route', 'menu', 3),
('用户管理', '/users', 'users', 'menu', 4),
('菜单管理', '/menus', 'menu', 'menu', 5),
('系统设置', '/settings', 'settings', 'menu', 6);

INSERT INTO system_configs (config_key, config_value, description) VALUES 
('gateway_port', '8080', '网关服务端口'),
('gateway_timeout', '30000', '网关请求超时时间(ms)'),
('max_connections', '1000', '最大连接数'),
('log_level', 'info', '日志级别');

-- 创建更新时间的触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为需要更新时间的表创建触发器
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_roles_updated_at BEFORE UPDATE ON roles FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_menus_updated_at BEFORE UPDATE ON menus FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_gateway_configs_updated_at BEFORE UPDATE ON gateway_configs FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_route_rules_updated_at BEFORE UPDATE ON route_rules FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_system_configs_updated_at BEFORE UPDATE ON system_configs FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();